package com.venta.autopartes.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.venta.autopartes.car.Address;
import com.venta.autopartes.car.Country;
import com.venta.autopartes.car.Location;
import com.venta.autopartes.car.State;
import com.venta.autopartes.car.Suburb;
import com.venta.autopartes.car.Town;
import com.venta.autopartes.car.TransmissionType;
import com.venta.autopartes.car.Zipcode;
import com.venta.autopartes.user.Phone;

public class LocationDAO extends BaseDAO {
	private final static String SELECT_ALL_COUNTRIES="SELECT 	venau_country_catalog_id AS ID,	venau_country_catalog_name AS NAME,venau_country_catalog_code AS CODE FROM SGDLS_T_country_catalog ORDER BY venau_country_catalog_name";
	private final static String SELECT_STATES_BY_COUNTRY="SELECT venau_state_catalog_id AS ID, venau_country_catalog_id_FK AS COUNTRY_ID,	venau_state_catalog_name AS NAME FROM SGDLS_T_state_catalog AS STATE where STATE.venau_country_catalog_id_FK=? ORDER BY venau_state_catalog_name";
	private final static String SELECT_TOWNS_BY_STATE="SELECT venau_town_catalog_id AS ID, venau_state_catalog_id_FK AS STATE_ID,venau_town_catalog_name AS NAME FROM SGDLS_T_town_catalog AS TOWN WHERE TOWN.venau_state_catalog_id_FK=? ORDER BY venau_town_catalog_name";
	private final static String SELECT_SUBURBS_BY_TOWN="SELECT venau_suburb_catalog_id AS ID,venau_town_catalog_id_FK AS TOWN_ID, venau_zipcode_catalog_id_FK AS ZIPCODE,venau_suburb_catalog_name AS NAME FROM SGDLS_T_suburb_catalog AS SUBURB WHERE SUBURB.venau_town_catalog_id_FK=? ORDER BY venau_suburb_catalog_name";
	private final static String SELECT_ZIPCODE_BY_SUBURB="SELECT venau_zipcode_catalog_zipcode AS ZIPCODE FROM SGDLS_T_zipcode_catalog AS ZIP, SGDLS_T_suburb_catalog AS SUBURB WHERE SUBURB.venau_suburb_catalog_id=? AND ZIP.venau_zipcode_catalog_id=SUBURB.venau_zipcode_catalog_id_FK";
	private final static String SELECT_BRANCH_ADDRESS_BY_BRANCH_ID="SELECT venau_branches_address_id AS ADDRESS_ID, venau_suburb_catalog_id_FK AS SUBURB_ID, venau_address_street_name AS STREET, venau_address_interior_number AS INTERIOR_NUMBER, venau_address_exterior_number AS EXTERIOR_NUMBER, venau_town_catalog_id_FK AS TOWN_ID,"
									+" venau_zipcode_catalog_id_FK AS ZIPCODE_ID,venau_suburb_catalog_name AS SUBURB_NAME, venau_state_catalog_id_FK AS STATE_ID, venau_town_catalog_name AS TOWN_NAME, venau_country_catalog_id_FK AS COUNTRY_ID,venau_state_catalog_name AS STATE_NAME, venau_country_catalog_id AS COUNTRY_ID, venau_country_catalog_name" 
			                        +" AS COUNTRY_NAME, venau_country_catalog_code AS COUNTRY_CODE, venau_zipcode_catalog_zipcode AS ZIPCODE"
									+" FROM SGDLS_T_branches_address  AS ADDRESS, SGDLS_T_suburb_catalog AS SUBURB, SGDLS_T_town_catalog AS TOWN, SGDLS_T_state_catalog AS STATE, SGDLS_T_country_catalog AS COUNTRY, SGDLS_T_zipcode_catalog AS ZIPCODE"
									+" WHERE ADDRESS.venau_suburb_catalog_id_FK=SUBURB.venau_suburb_catalog_id AND SUBURB.venau_town_catalog_id_FK=TOWN.venau_town_catalog_id AND SUBURB.venau_zipcode_catalog_id_FK=ZIPCODE.venau_zipcode_catalog_id AND TOWN.venau_state_catalog_id_FK=STATE.venau_state_catalog_id " 
									+" AND STATE.venau_country_catalog_id_FK=COUNTRY.venau_country_catalog_id AND ADDRESS.venau_branch_id_FK=?";
	
	private final static String SELECT_USER_ADDRESS_BY_USER_ID="SELECT venau_address_id AS ADDRESS_ID, venau_suburb_catalog_id_FK AS SUBURB_ID, venau_address_street_name AS STREET, venau_address_interior_number AS INTERIOR_NUMBER, venau_address_exterior_number AS EXTERIOR_NUMBER, venau_town_catalog_id_FK AS TOWN_ID,"
	//private final static String SELECT_USER_ADDRESS_BY_USER_ID="SELECT venau_users_address_id AS ADDRESS_ID, venau_suburb_catalog_id_FK AS SUBURB_ID, venau_address_street_name AS STREET, venau_address_interior_number AS INTERIOR_NUMBER, venau_address_exterior_number AS EXTERIOR_NUMBER, venau_town_catalog_id_FK AS TOWN_ID,"
			+" venau_zipcode_catalog_id_FK AS ZIPCODE_ID,venau_suburb_catalog_name AS SUBURB_NAME, venau_state_catalog_id_FK AS STATE_ID, venau_town_catalog_name AS TOWN_NAME, venau_country_catalog_id_FK AS COUNTRY_ID,venau_state_catalog_name AS STATE_NAME, venau_country_catalog_id AS COUNTRY_ID, venau_country_catalog_name" 
            +" AS COUNTRY_NAME, venau_country_catalog_code AS COUNTRY_CODE, venau_zipcode_catalog_zipcode AS ZIPCODE"
			+" FROM SGDLS_T_address  AS ADDRESS, SGDLS_T_suburb_catalog AS SUBURB, SGDLS_T_town_catalog AS TOWN, SGDLS_T_state_catalog AS STATE, SGDLS_T_country_catalog AS COUNTRY, SGDLS_T_zipcode_catalog AS ZIPCODE"
			//+" FROM SGDLS_T_users_address  AS ADDRESS, SGDLS_T_suburb_catalog AS SUBURB, SGDLS_T_town_catalog AS TOWN, SGDLS_T_state_catalog AS STATE, SGDLS_T_country_catalog AS COUNTRY, SGDLS_T_zipcode_catalog AS ZIPCODE"
			+" WHERE ADDRESS.venau_suburb_catalog_id_FK=SUBURB.venau_suburb_catalog_id AND SUBURB.venau_town_catalog_id_FK=TOWN.venau_town_catalog_id AND SUBURB.venau_zipcode_catalog_id_FK=ZIPCODE.venau_zipcode_catalog_id AND TOWN.venau_state_catalog_id_FK=STATE.venau_state_catalog_id " 
			+" AND STATE.venau_country_catalog_id_FK=COUNTRY.venau_country_catalog_id AND ADDRESS.venau_user_id_FK=?";
	
	private final static String SELECT_PHONE_TYPES="SELECT venau_phone_catalog_id AS ID, venau_phone_catalog_type_name AS PHONE_TYPE FROM SGDLS_T_phone_catalog AS PHONE_CATALOG ORDER BY venau_phone_catalog_type_name";
	
	public static List<Country> getCountriesList(){
		List<Country> countriesList=new ArrayList<Country>();
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_ALL_COUNTRIES);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Country country = new Country(resultSet.getInt("ID"), resultSet.getString("NAME"),resultSet.getInt("CODE"));
				countriesList.add(country);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		 for ( Country country: countriesList) {
			 country.setStates(getStatesList(country.getCountryId()) );
		     
		    }
		
		return countriesList;
	}
	
	public static List<State> getStatesList(int countryId){
		List<State> statesList=new ArrayList<State>();
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_STATES_BY_COUNTRY);
			preparedStatement.setInt(1,countryId);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				State state = new State(resultSet.getInt("ID"), resultSet.getString("NAME"),resultSet.getInt("COUNTRY_ID"));
				statesList.add(state);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		 for ( State state: statesList ) {
			 state.setTowns(getTownsList(state.getStateId()) );
		     
		    }
		
		return statesList;
	}
	
	public static List<Town> getTownsList(int stateId){
		List<Town> townsList=new ArrayList<Town>();
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_TOWNS_BY_STATE);
			preparedStatement.setInt(1, stateId);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Town town = new Town(resultSet.getInt("ID"), resultSet.getString("NAME"),resultSet.getInt("STATE_ID"));
				townsList.add(town);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		 for ( Town town:townsList) {
			 town.setSuburbs(getSuburbsList(town.getTownId()) );
		     
		    }
		
		return townsList;
	}
	
	public static List<Suburb> getSuburbsList(int townId){
		List<Suburb> suburbsList=new ArrayList<Suburb>();
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_SUBURBS_BY_TOWN);
			preparedStatement.setInt(1, townId);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Suburb suburb = new Suburb(resultSet.getInt("ID"), resultSet.getString("NAME"),resultSet.getInt("TOWN_ID"));
				Zipcode zipcode= new Zipcode(resultSet.getInt("ZIPCODE"));
				suburb.setZipcode(zipcode);
				suburbsList.add(suburb);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		 for ( Suburb suburb:suburbsList) {
			 suburb.getZipcode().setZipcode(getZipcodeBySuburb(suburb.getSuburbId()));
		    }
		
		return suburbsList;
	}
	
	public static String getZipcodeBySuburb(int suburbId){
		String zipcode=null;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_ZIPCODE_BY_SUBURB);
			preparedStatement.setInt(1, suburbId);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				zipcode=resultSet.getString("ZIPCODE");
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return zipcode;
	}
	
	public static Address getBranchAddressById(int branchId){
		Address address=null;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_BRANCH_ADDRESS_BY_BRANCH_ID);
			preparedStatement.setInt(1, branchId);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Country country=new Country(resultSet.getInt("COUNTRY_ID"), resultSet.getString("COUNTRY_NAME"), resultSet.getInt("COUNTRY_CODE"));
				State state= new State(resultSet.getInt("STATE_ID"), resultSet.getString("STATE_NAME"), resultSet.getInt("COUNTRY_ID"));
				Town town= new Town(resultSet.getInt("TOWN_ID"), resultSet.getString("TOWN_NAME"), resultSet.getInt("STATE_ID"));
				Suburb suburb= new Suburb(resultSet.getInt("SUBURB_ID"), resultSet.getString("SUBURB_NAME"), resultSet.getInt("TOWN_ID"));
				Zipcode zipcode= new Zipcode(resultSet.getInt("ZIPCODE_ID"));
				zipcode.setZipcode(resultSet.getString("ZIPCODE"));
				suburb.setZipcode(zipcode);
				address=new Address();
				address.setExteriorNumber(resultSet.getString("EXTERIOR_NUMBER"));
				address.setInteriorNumber(resultSet.getString("INTERIOR_NUMBER"));
				address.setStreet(resultSet.getString("STREET"));
				address.setCountry(country);
				address.setState(state);
				address.setSuburb(suburb);
				address.setTown(town);
				address.setZip(zipcode);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return address;
	}
	
	public static Address getUserAddressById(int userId){
		Address address=null;
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_USER_ADDRESS_BY_USER_ID);
			preparedStatement.setInt(1, userId);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Country country=new Country(resultSet.getInt("COUNTRY_ID"), resultSet.getString("COUNTRY_NAME"), resultSet.getInt("COUNTRY_CODE"));
				State state= new State(resultSet.getInt("STATE_ID"), resultSet.getString("STATE_NAME"), resultSet.getInt("COUNTRY_ID"));
				Town town= new Town(resultSet.getInt("TOWN_ID"), resultSet.getString("TOWN_NAME"), resultSet.getInt("STATE_ID"));
				Suburb suburb= new Suburb(resultSet.getInt("SUBURB_ID"), resultSet.getString("SUBURB_NAME"), resultSet.getInt("TOWN_ID"));
				Zipcode zipcode= new Zipcode(resultSet.getInt("ZIPCODE_ID"));
				zipcode.setZipcode(resultSet.getString("ZIPCODE"));
				suburb.setZipcode(zipcode);
				address=new Address();
				address.setExteriorNumber(resultSet.getString("EXTERIOR_NUMBER"));
				address.setInteriorNumber(resultSet.getString("INTERIOR_NUMBER"));
				address.setStreet(resultSet.getString("STREET"));
				address.setCountry(country);
				address.setState(state);
				address.setSuburb(suburb);
				address.setTown(town);
				address.setZip(zipcode);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return address;
	}
	
	public static List<Phone> getPhoneList(){
		List<Phone> phoneList=new ArrayList<Phone>();
		
		connection = com.venta.autopartes.utils.Connection.getConnection();
		try {
			preparedStatement = connection.prepareStatement(SELECT_PHONE_TYPES);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Phone phone = new Phone(resultSet.getInt("ID"), resultSet.getString("PHONE_TYPE"));
				phoneList.add(phone);
			}

		} catch (SQLException sqlException) {
			System.out.println("(Class)LocationDAO - (ErrorType)sqlException");
			sqlException.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception exception) {
				System.out.println("(Class)LocationDAO - (ErrorType)exception");
				exception.printStackTrace();
			}
		}finally{
			closeConnection();
		}
		
		return phoneList;
	}
	
}
